package com.ujiuye.demo.impl;

import com.ujiuye.demo.StudentDao;
import com.ujiuye.entity.Student;
import com.ujiuye.util.JdbcUtil;
import com.ujiuye.util.PackageUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    QueryRunner qr = JdbcUtil.getQr();

    @Override
    public List<Student> getAllS() {
        List<Student> list = null;
        String sql = "select * from tb_student";
        try {
            list = qr.query(sql, new BeanListHandler<>(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCountRows() {
        int countRows = 0;
        String sql = "select count(*) from tb_student";
        try {
            countRows = (int) (long) qr.query(sql, new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return countRows;
    }

    @Override
    public List<Student> getStudentByPage(PackageUtil p) {
        List<Student> list = null;
        String sql = "select * from tb_student limit ?,?";
        try {
            list = qr.query(sql, new BeanListHandler<>(Student.class), p.getIndex(), p.getRows());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int addStudent(Student stu) {
        int rows = 0;
        String sql = "insert into tb_student(sname,sage,ssex,semail,sphoto) value(?,?,?,?,?)";
        try {
            rows = qr.update(sql, stu.getSname(), stu.getSage(), stu.getSsex(), stu.getSemail(), stu.getSphoto());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public int delStudent(int sid) {
        int rows = 0;
        String sql = "delete from tb_student where sid=?";
        try {
            rows = qr.update(sql, sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public Student getStudentById(int sid) {
        Student stu = null;
        String sql = "select * from tb_student where sid=?";
        try {
            stu = qr.query(sql, new BeanHandler<>(Student.class),sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }

    @Override
    public int editStudent(Student stu) {
        int rows = 0;
        String sql = "update tb_student set sname=?,sage=?,ssex=?,semail=?,sphoto=? where sid=?";
        try {
            rows = qr.update(sql, stu.getSname(), stu.getSage(), stu.getSsex(), stu.getSemail(), stu.getSphoto(),stu.getSid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public int getCountRowsBySname(String sname) {
        int countRows = 0;
        String sql = "select count(*) from tb_student where sname like ?";
        try {
            countRows = (int)(long)qr.query(sql,new ScalarHandler(), "%"+sname+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return countRows;
    }

    @Override
    public List<Student> getStudentBySname(PackageUtil pu, String sname) {
        List<Student> list = null;
        String sql = "select * from tb_student where sname like ? limit ?,?";
        try {
            list = qr.query(sql, new BeanListHandler<>(Student.class),"%"+sname+"%", pu.getIndex(), pu.getRows());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
